package com.seafood.repository;

import com.seafood.entity.ColdChainRecord;
import com.seafood.entity.Order;
import com.seafood.entity.Product;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 冷链运输记录Repository
 */
@Repository
public interface ColdChainRecordRepository extends JpaRepository<ColdChainRecord, Long> {
    
    /**
     * 根据运输单号查找记录
     */
    Optional<ColdChainRecord> findByTransportNumber(String transportNumber);
    
    /**
     * 根据订单查找冷链记录
     */
    List<ColdChainRecord> findByOrder(Order order);
    
    /**
     * 根据订单ID查找冷链记录
     */
    List<ColdChainRecord> findByOrderId(Long orderId);
    
    /**
     * 根据产品查找冷链记录
     */
    List<ColdChainRecord> findByProduct(Product product);
    
    /**
     * 根据产品ID查找冷链记录
     */
    List<ColdChainRecord> findByProductId(Long productId);
    
    /**
     * 根据运输状态查找记录
     */
    @Query("SELECT c FROM ColdChainRecord c WHERE c.status = :status")
    Page<ColdChainRecord> findByStatus(@Param("status") ColdChainRecord.TransportStatus status, Pageable pageable);
    
    /**
     * 查找正在运输中的记录
     */
    @Query("SELECT c FROM ColdChainRecord c WHERE c.status IN ('IN_TRANSIT', 'STARTED')")
    List<ColdChainRecord> findActiveTransports();
    
    /**
     * 根据运输公司查找记录
     */
    Page<ColdChainRecord> findByTransportCompanyContaining(String transportCompany, Pageable pageable);
    
    /**
     * 根据时间范围查找记录
     */
    @Query("SELECT c FROM ColdChainRecord c WHERE c.startTime >= :startTime AND c.endTime <= :endTime")
    List<ColdChainRecord> findByTimeRange(@Param("startTime") LocalDateTime startTime, 
                                         @Param("endTime") LocalDateTime endTime);
    
    /**
     * 查找有报警的运输记录
     */
    @Query("SELECT DISTINCT c FROM ColdChainRecord c JOIN c.alerts a WHERE a.isResolved = false")
    List<ColdChainRecord> findRecordsWithActiveAlerts();
    
    /**
     * 根据质量评分范围查找记录
     */
    @Query("SELECT c FROM ColdChainRecord c WHERE c.qualityScore >= :minScore AND c.qualityScore <= :maxScore")
    Page<ColdChainRecord> findByQualityScoreRange(@Param("minScore") Double minScore, 
                                                  @Param("maxScore") Double maxScore, 
                                                  Pageable pageable);
    
    /**
     * 统计各状态的运输记录数量
     */
    @Query("SELECT c.status, COUNT(c) FROM ColdChainRecord c GROUP BY c.status")
    List<Object[]> countByStatus();
    
    /**
     * 查找最近创建的记录
     */
    Page<ColdChainRecord> findByOrderByCreatedAtDesc(Pageable pageable);
}